package com.tencent.qplus.paddata.stat;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.tencent.qplus.paddata.db.DataSourceManager;
import com.tencent.qplus.paddata.util.JdbcUtils;


public class InitTablesJob extends Job{
	
	
	private static Connection conn;
	
	@Override
	public void init(){
		try {
			conn = DataSourceManager.getInstance().getDataSource("paddatadb").getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void uninit(){
		JdbcUtils.release(conn);
	}
	
	
	private void createPadversiondatauinonlinetimeTable(){
		ResultSet rs = null;
		Statement st = null;
		PreparedStatement pst = null;
		try {
			st = conn.createStatement();
			String sql = null;
			rs = st.executeQuery("show create table padversiondata.uinonlinetime");
			if(rs.next()){
				sql = rs.getString(2);
				sql = sql.replaceFirst("`uinonlinetime`", "if not exists padversiondata.uinonlinetime_?");
				pst = conn.prepareStatement(sql);
				for(int i=0;i<100;i++){
					pst.setInt(1, i);
					pst.addBatch();
				}
				pst.executeBatch();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtils.release(rs);
			JdbcUtils.release(pst);
			JdbcUtils.release(st);
		}
	}
	
	
	private void createPadversiondataUinloginTable(){
		ResultSet rs = null;
		Statement st = null;
		PreparedStatement pst = null;
		try {
			st = conn.createStatement();
			String sql = null;
			rs = st.executeQuery("show create table padversiondata.uinlogin");
			if(rs.next()){
				sql = rs.getString(2);
				sql = sql.replaceFirst("`uinlogin`", "if not exists padversiondata.uinlogin_?");
				pst = conn.prepareStatement(sql);
				for(int i=0;i<100;i++){
					pst.setInt(1, i);
					pst.addBatch();
				}
				pst.executeBatch();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtils.release(rs);
			JdbcUtils.release(pst);
			JdbcUtils.release(st);
		}
	}
	
	private void dropPadversiondataUinloginTable(){
		Statement st = null;
		try {
			st = conn.createStatement();
			for(int i=0;i<100;i++){
				st.execute("drop table if exists padversiondata.uinlogin_"+i);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtils.release(st);
		}
	}
	
	private void createUserDataTables(){
		ResultSet rs = null;
		Statement st = null;
		PreparedStatement pst = null;
		try {
			st = conn.createStatement();
			String sql = null;
			rs = st.executeQuery("show create table paduser.userdata");
			if(rs.next()){
				sql = rs.getString(2);
				sql = sql.replaceFirst("`userdata`", "if not exists paduser.userdata_?");
				pst = conn.prepareStatement(sql);
				for(int i=0;i<1000;i++){
					pst.setInt(1, i);
					pst.addBatch();
				}
				pst.executeBatch();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtils.release(rs);
			JdbcUtils.release(pst);
			JdbcUtils.release(st);
		}
	}
	
	
	private void createTables(){
		ResultSet rs = null;
		Statement st = null;
		PreparedStatement pst = null;
		try {
			st = conn.createStatement();
			String sql = null;
			rs = st.executeQuery("show create table uinlogin");
			if(rs.next()){
				sql = rs.getString(2);
				sql = sql.replaceFirst("`uinlogin`", "if not exists uinlogin_?");
				pst = conn.prepareStatement(sql);
				for(int i=0;i<100;i++){
					pst.setInt(1, i);
					pst.addBatch();
				}
				pst.executeBatch();
			}
			
			rs = st.executeQuery("show create table userinfo");
			if(rs.next()){
				sql = rs.getString(2);
				sql = sql.replaceFirst("`userinfo`", "if not exists userinfo_?");
				pst = conn.prepareStatement(sql);
				for(int i=0;i<100;i++){
					pst.setInt(1, i);
					pst.addBatch();
				}
				pst.executeBatch();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtils.release(rs);
			JdbcUtils.release(pst);
			JdbcUtils.release(st);
		}
	}
	
	private void dropTables(){
		PreparedStatement pst = null;
		try {
				String sql = "drop table if exists uinlogin_?";
				pst = conn.prepareStatement(sql);
				for(int i=0;i<100;i++){
					pst.setInt(1, i);
					pst.addBatch();
				}
				pst.executeBatch();
			
				sql = "drop table if exists userinfo_?";
				pst = conn.prepareStatement(sql);
				for(int i=0;i<100;i++){
					pst.setInt(1, i);
					pst.addBatch();
				}
				pst.executeBatch();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtils.release(pst);
		}
	}
	
	@SuppressWarnings("unused")
	private void truncateTables(){
		PreparedStatement pst = null;
		try {
				String sql = "truncate table uinlogin_?";
				pst = conn.prepareStatement(sql);
				for(int i=0;i<100;i++){
					pst.setInt(1, i);
					pst.addBatch();
				}
				pst.executeBatch();
			
				sql = "truncate table userinfo_?";
				pst = conn.prepareStatement(sql);
				for(int i=0;i<100;i++){
					pst.setInt(1, i);
					pst.addBatch();
				}
				pst.executeBatch();
				
				pst.execute("truncate table devices");
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtils.release(pst);
		}
	}
	
	
	@Override
	public void run() {
		init();
		//dropTables();
		//createTables();
		//createUserDataTables();
		//dropPadversiondataUinloginTable();
		//createPadversiondataUinloginTable();
		
		createPadversiondatauinonlinetimeTable();
		uninit();
	}

	public void stat(boolean isRunInMainThread,String[] args){
		if(args!=null && args.length==1 && dateUtils.isDateStr(args[0])){
			this.statDate = args[0];
		}
		run();
	}
	
	
	public void test(){
		init();
		//truncateTables();
		//dropTables();
		//createTables();
		//createPadversiondataUinloginTable();
		uninit();
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		InitTablesJob job = new InitTablesJob();
		job.run();

	}

	@Override
	public void stat(String[] args) {
		// TODO Auto-generated method stub
		
	}
}
